package DAL.JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import DAL.Entites.*;

public class GenreJDBC {
	Connection conn;

	String tableName = "genres"; 

	public GenreJDBC(Connection connection) {
		this.conn = connection;
	}
	
	public void insert(String Name) {
		String SQL = String.format("insert into %s values(null,'%s')",
				tableName, Name);
		
		try {
			Statement st = this.conn.createStatement();
			st.execute(SQL);
			st.close();
			System.out.println("Created Record");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return;
	}
	
	private Genre mapRow(ResultSet rs) throws SQLException {
		Genre genre = new Genre(rs.getInt("Id"));
		genre.setName(rs.getString("GenreName"));
		System.out.println("mapRow");

		return genre;
	}
	
	public List<Genre> listGenres() {
		List<Genre> genreslist = new ArrayList<Genre>();
		String SQL = String.format("select * from %s", tableName);
		try {
			Statement st = conn.createStatement();
			for (ResultSet rs = st.executeQuery(SQL); rs.next();) {
				genreslist.add(mapRow(rs));
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}

		return genreslist;
	}
	
	
	@SuppressWarnings("finally")
	public boolean updateGenre(Genre genre)
	{
		boolean updated = false;
		String Query = String.format(
				"update %s set Name = '%s' where id = %s",
				tableName,
				genre.getName(),
				Integer.toString(genre.getId())
		);
		try {
			Statement st = conn.createStatement();
			st.execute(Query);
			st.close();
			updated = true;
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		finally{
			return updated;
		}
		
	}
	
	public void removeGenre(int id) {
		String SQL = String.format("delete * from %s where id = %s", tableName, id);
		try {
			Statement st = conn.createStatement();
			st.execute(SQL);
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}
	
	@SuppressWarnings("finally")
	public Genre getGenreByName(String name)
	{
		String SQL = String.format("select * from %s where GenreName = '%s'", tableName, name);
		Genre genre = null;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(SQL);
			if(rs.next())
			{
				genre = mapRow(rs);
			}
			st.close();
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		finally
		{
			return genre;
		}
	}
}
